1. Field of the Invention
The present invention relates to a method, system, and program for providing optimistic concurrency with scrollable cursors in a database.
2. Description of the Related Art
Prior art database programs include a feature referred to as cursors. A cursor is a named control structure used by an application program to point to a row of interest within some set of rows and to retrieve rows from the set, possibly making updates and deletions. A cursor points to rows from a database table that satisfy a structured query language (SQL) query against the table. The rows in the table that satisfy the SQL query comprise a result table of data. The SQL query includes an SQL SELECT statement and a WHERE clause to qualify rows according to a predicate. An application can then access data on a row-by-row basis from the result table.
If the result table is static and not updateable, then the result table may be materialized in a workfile. Alternatively, the cursor may point directly to the rows in the base table. In such case, the result table is not materialized in a workfile and the cursor is updateable when the base table is updated.
When a cursor is opened or initialized, the current row position of the cursor is the first record in the result table. The application program may then issue fetch commands to move the current row position and fetch forward or backward by one or more rows or from the first or last row by one or more rows. In current implementations, if the cursor is static, then the result table cannot be updated so that once it is created no rows will be added or removed, and no values in any rows will change. Further, a static cursor is not affected by other applications accessing or updating the data records in the underlying database table from which the result set was generated. Still further, in prior art static cursor implementations, the cursor is also read-only so that it is not possible for the application to change any values in the cursor result table.
In the prior art, after the cursor is opened and a FETCH request issued to access a row, the cursor obtains a lock on the page including the requested row or the row in the base table that is being fetched. This lock on the page or row of the base table is maintained until the next fetch request or when the cursor is closed. The purpose of this lock is to ensure that the row that was fetched and returned to the application program is not changed. One problem with this prior art approach is that maintaining the lock on the fetched page or row in the base table provides low concurrency because other applications cannot modify the database page or row while the lock is held. Thus, in the prior art, the best concurrency that can be obtained is by only locking the row, not the page including the row. However, even with this approach, there is still low concurrency for the row.
Thus, there is a need in the art for a technique for improving concurrency when cursors are used.
Preferred embodiments disclose a method, system, and program for performing a cursor operation. A result table is generated including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause. A command is received to perform a cursor operation with respect to a subject row in the result table. A lock is obtained on a row in the base table corresponding to the subject row in the result table as part of performing the operation specified in the received command. An operation specified in the received command is performed with respect to the subject row after obtaining the lock. The lock on the row in the base table is released after performing the operation.
In further embodiments, the command comprises a fetch command. In such case, performing the operation further comprises updating the subject row in the result table with the locked row in the base table if the locked row in the base table satisfies the query predicate clause. The updated row in the result table is then returned in response to the fetch command.
In still further embodiments, the command may comprise a delete or update command to delete or update a subject row in the result table. In such case, if the locked row in the base table satisfies the query predicate clause, then the subject row in the result table is deleted or updated.
With preferred embodiments, a lock is obtained on a row in the base table for the duration of the cursor operation, e.g., fetch, update, delete, etc. Once the FETCH operation has completed, the lock is released to provide high concurrency for the row in the base table. Data integrity is maintained because a requested operation following a release of the lock is not performed until various checks on the row in the result table are made to ensure that the base table row has not been modified in a manner that would preclude the fetch, delete or update operation from occurring in order to maintain data integrity.